package com.indobrains.uud1945.database;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DatabaseAdapter  {
	public final String CHAPTER_ID = "_id";
	public final String CHAPTER_NAME = "title";
	public final String CHAPTER_DESC = "description";
	public final String CHAPTER_NOTE = "note";
	public final String CHAPTER_OLDER = "older_version";
	public final String CLAUSE_ID = "_id";
	public final String CLAUSE_TITLE = "title";
	public final String CLAUSE_CHAPTER = "chapter_id";
	public final String CLAUSE_AMANDEMEN = "amandemen";
	public final String AMANDEMEN_ID = "_id";
	public final String AMANDEMEN_DESC = "description";
	
	public final String CONTENT_ID="_id";
	public final String CONTENT_CLAUSE="clause_id";
	public final String CONTENT_BULLET="bullet";
	public final String CONTENT_CONTENT="content";
	public final String CONTENT_AMANDEMEN="amandemen";
	public final String CONTENT_OLDER="older_version";
	public static final int DIALOG_DOWNLOAD_PROGRESS = 0; 
	private static final String CHAPTER_TABLE = "_chapters";
	private static final String CLAUSE_TABLE = "_clauses";
	private static final String AMANDEMEN_TABLE = "_amandemens";
	private static final String CONTENT_TABLE = "_paragraphs";
	 
	private Context mContext;
	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;
	 
	 
	public DatabaseAdapter(Context context) {
		this.mContext=context;
		// TODO Auto-generated constructor stub
	}
	
	public DatabaseAdapter Open() throws SQLException {
		dbHelper=new DatabaseHelper(mContext);
		 
		database = dbHelper.getWritableDatabase();
		return this;
	}
	public Cursor getAllChapter(){
		return database.query(CHAPTER_TABLE,new String[] {CHAPTER_ID,
				CHAPTER_NAME,CHAPTER_DESC,CHAPTER_NOTE,CHAPTER_OLDER},
				null,null,null,null,null);
	}
	public Cursor getClauses(int ChapterID){
		String s_query="SELECT a.* FROM "+CLAUSE_TABLE+" a"+
						" WHERE a.chapter_id="+Integer.toString(ChapterID)+" "+
						" order by a."+CLAUSE_ID+" ASC";
		//return database.rawQuery(s_query,new String[]{CLAUSE_ID,CLAUSE_TITLE,AMANDEMEN_DESC});
		return database.rawQuery(s_query, null);
	}
	public Cursor getContents(int clauseID){
		String s_query="SELECT a.*,b.description "+
						"FROM "+CONTENT_TABLE+" a,"+
						AMANDEMEN_TABLE+" b"+
						" WHERE a.clause_id="+Integer.toString(clauseID)+""+
						" and a.amandemen=b._id "+
						" order by a."+CONTENT_BULLET+" ASC";
		return database.rawQuery(s_query,null);
	}
	public Cursor searchContents(String keyword){
		String s_query="SELECT a.*,b.description,c.title,d.title as chapter_name,d.description as chapter_description "+
						"FROM "+CONTENT_TABLE+" a,"+
						AMANDEMEN_TABLE+" b,"+CLAUSE_TABLE+" c,"+CHAPTER_TABLE+" d"+
						" WHERE a.content like '%"+keyword+"%'"+
						" and a.amandemen=b._id and a.clause_id=c._id and c.chapter_id=d._id"+
						" order by c."+CLAUSE_TITLE+" ASC";
		return database.rawQuery(s_query,null);
	}
	public int getClauseCount(int chapter_id){
		String query="select count(*) as count from _clauses where chapter_id="+Integer.toString(chapter_id);
		Cursor c=database.rawQuery(query, null);
		c.moveToFirst();
		return c.getInt(0);
	}
	public int getParagraphCount(int clause_id){
		String query="select count(*) as count from _paragraphs where clause_id="+Integer.toString(clause_id);
		Cursor c=database.rawQuery(query, null);
		c.moveToFirst();
		return c.getInt(0);
	}
	public void ExecSQL(String command){
		try {
		database.execSQL(command);
		} catch (Exception ex){
			 Log.e("INSERT ERROR :",ex.getMessage());
		}
	}
	 
	public Cursor fetchContent(long rowId) throws SQLException {
		Cursor mCursor= database.query(true,CONTENT_TABLE,new String[] {CONTENT_ID,
				CONTENT_CLAUSE,CONTENT_CONTENT,CONTENT_BULLET,CONTENT_AMANDEMEN},
				CONTENT_ID+"="+Long.toString(rowId),null,null,null,null,null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	public Cursor fetchClauses(long rowId) throws SQLException {
		Cursor mCursor= database.query(true,CLAUSE_TABLE,new String[] {CLAUSE_ID,
				CLAUSE_TITLE,CLAUSE_AMANDEMEN},
				CLAUSE_ID+"="+Long.toString(rowId),null,null,null,null,null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	public Cursor fetchAmandemen(long rowId) throws SQLException {
		Cursor mCursor= database.query(true,AMANDEMEN_TABLE,new String[] {AMANDEMEN_ID,
				AMANDEMEN_DESC},
				AMANDEMEN_ID+"="+Long.toString(rowId),null,null,null,null,null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	public Cursor fetchChapter(long rowId) throws SQLException {
		Cursor mCursor= database.query(true,CHAPTER_TABLE,new String[] {CHAPTER_ID,
				CHAPTER_NAME,CHAPTER_DESC,CHAPTER_NOTE,CHAPTER_OLDER},
				CHAPTER_ID+"="+Long.toString(rowId),null,null,null,null,null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
}
